//
//  HistoryMigration.swift
//  hello
//
//  Created by 赵康 on 2024/11/11.
//

import Fluent
import FluentSQL
extension History {
	struct Migration: AsyncMigration {
		var name: String { "HistoryMigration" }
		
		func prepare(on database: any Database) async throws {
			try await database.schema(History.schema)
				.id()
				.field(History.FieldKeys.user, .uuid, .required, .references(User.schema, .id))
				.field(History.FieldKeys.audio, .uuid, .required, .references(Audio.schema, .id))
				.field(
					History.FieldKeys.progress,
					.double,
					.required,
					.custom("DEFAULT 0")
				)
				.field(
					History.FieldKeys.duration,
					.double,
					.required,
					.custom("DEFAULT 0")
				)
				.field(History.FieldKeys.isFinished, .bool, .required, .custom("DEFAULT FALSE"))
				.field(History.FieldKeys.lastListenedAt, .datetime)
				.field(History.FieldKeys.createdAt, .datetime, .required)
				.field(
					History.FieldKeys.listenedDuration,
					.double,
					.required,
					.custom("DEFAULT 0")
				)
				.create()
		}
		func revert(on database: any Database) async throws {
			try await database.schema(History.schema).delete()
		}
	}
}
extension History {
	struct AddDeleteFieldsToHistoryMigration: AsyncMigration {
		var name: String { "AddDeleteFieldsToHistoryMigration" }
		
		func prepare(on database: any Database) async throws {
			try await database.schema(History.schema)
				.field(History.FieldKeys.deletedAt, .datetime)
				.field(History.FieldKeys.deleteStatus, .int, .required, .custom("DEFAULT 0"))
				.update()
		}
		
		func revert(on database: any Database) async throws {
			try await database.schema(History.schema)
				.deleteField(History.FieldKeys.deletedAt)
				.deleteField(History.FieldKeys.deleteStatus)
				.update()
		}
	}
}
extension History {
	struct AddListenStatusToHistoryMigration: AsyncMigration {
		var name: String { "AddListenStatusToHistoryMigration" }
		
		func prepare(on database: any Database) async throws {
			try await database.schema(History.schema)
				.field(History.FieldKeys.listenStatus, .int, .required, .custom("DEFAULT 0"))
				.field(History.FieldKeys.completedCount, .int, .required, .custom("DEFAULT 0"))
				.update()
		}
		
		func revert(on database: any Database) async throws {
			try await database.schema(History.schema)
				.deleteField(History.FieldKeys.listenStatus)
				.deleteField(History.FieldKeys.completedCount)
				.update()
		}
	}
}
extension History {
	struct AddHistoryIndexes: AsyncMigration {
		var name: String { "AddHistoryIndexes" }
		
		func prepare(on database: any Database) async throws {
			guard let sql = database as? SQLDatabase else {
				throw FluentError.missingField(name: "Database must be SQL compatible")
			}
			
				// 创建用户时间索引
			try await sql.raw("""
								CREATE INDEX user_time_idx 
								ON \(unsafeRaw: History.schema) (user, lastListenedAt)
						""").run()
			
				// 创建音频时间索引
			try await sql.raw("""
								CREATE INDEX audio_time_idx 
								ON \(unsafeRaw: History.schema) (audio, lastListenedAt)
						""").run()
		}
		
		func revert(on database: any Database) async throws {
			guard let sql = database as? SQLDatabase else {
				throw FluentError.missingField(name: "Database must be SQL compatible")
			}
			
				// 删除索引
			try await sql.raw("""
								DROP INDEX IF EXISTS user_time_idx ON \(unsafeRaw: History.schema)
						""").run()
			
			try await sql.raw("""
								DROP INDEX IF EXISTS audio_time_idx ON \(unsafeRaw: History.schema)
						""").run()
		}
	}
}


